--microsoft sql server-09

--SELECT SCHEMA  
SELECT tb1.name AS NAME,
	tb2.create_date AS CRTM,
	1 AS INUSE
FROM sys.schemas tb1 LEFT JOIN 
	sys.objects tb2 ON tb1.schema_id = tb2.object_id 

--SELECT TABLE   
SELECT NAME=tb4.name + '.' + tb1.name, 
	tb1.create_date AS CRTM,
	1 AS INUSE
FROM sys.tables tb1 LEFT JOIN 
	sys.objects tb2 ON tb1.parent_object_id=tb2.object_id
	LEFT JOIN sys.schemas tb4 on tb4.schema_id=tb1.schema_id

--SELECT TRIGGER 
SELECT tb1.name AS NAME, 
	OWTB=tb4.name+'.'+tb2.name, 
	CAST(tb3.TEXT AS TEXT) AS SQL, 
	tb1.create_date AS CRTM,
	1 AS INUSE
FROM sys.triggers tb1 LEFT JOIN 
	sys.objects tb2 ON tb1.parent_id=tb2.object_id
	LEFT JOIN sys.syscomments tb3 on tb3.id=tb1.object_id
	LEFT JOIN sys.schemas tb4 on tb4.schema_id=tb2.schema_id

--SELECT VIEW
SELECT NAME=tb4.name + '.' + tb1.name, 
	tb3.TEXT AS SQL, 
	tb1.create_date AS CRTM,
	1 AS INUSE
FROM sys.views tb1 LEFT JOIN 
	sys.objects tb2 ON tb1.parent_object_id=tb2.id
	LEFT JOIN sys.syscomments tb3 on tb3.ID=tb1.object_id
	LEFT JOIN sys.schemas tb4 on tb4.schema_id=tb1.schema_id


--SELECT VALIDATE  
SELECT 1

--DEPLOY TRIGGER DELETE
CREATE TRIGGER DITS.DATA_MONITOR_DTG ON DITS.DATA_MONITOR_TB  FOR DELETE AS
BEGIN
	DECLARE @DELETE_COUNT int
	DECLARE @OLDFIELDVALUE sysname
	SELECT @DELETE_COUNT = COUNT(*) FROM DELETED
	IF @DELETE_COUNT <> 0 
	BEGIN
		IF @DELETE_COUNT = 1
		BEGIN
			SELECT @OLDFIELDVALUE = DATA_ID FROM DELETED
			INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
				VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, @OLDFIELDVALUE, 1)
		END
		ELSE
		BEGIN
			DECLARE tmp_delete CURSOR FOR SELECT DATA_ID FROM DELETED
			OPEN tmp_delete
			FETCH NEXT FROM tmp_delete INTO @OLDFIELDVALUE 
			WHILE (@@FETCH_STATUS = 0)
			BEGIN
				INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
					VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, @OLDFIELDVALUE, 1)
				FETCH NEXT FROM tmp_delete INTO @OLDFIELDVALUE
			END
			CLOSE tmp_delete
			DEALLOCATE tmp_delete
		END
	END
END

--UNDEPLOY TRIGGER DELETE
DROP TRIGGER DITS.DATA_MONITOR_DTG

--DEPLOY TRIGGER INSERT
CREATE TRIGGER DITS.DATA_MONITOR_ITG ON DITS.DATA_MONITOR_TB FOR INSERT AS 
BEGIN
	DECLARE @INSERT_COUNT int
	DECLARE @OLDFIELDVALUE sysname
	SELECT @INSERT_COUNT = COUNT(*) FROM INSERTED
	IF @INSERT_COUNT <> 0 
	BEGIN
		IF @INSERT_COUNT = 1 
		BEGIN
			SELECT @OLDFIELDVALUE = DATA_ID FROM INSERTED
			INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
				VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, @OLDFIELDVALUE, 1)
		END
		ELSE
		BEGIN
			DECLARE tmp_insert CURSOR FOR SELECT DATA_ID FROM INSERTED
			OPEN tmp_insert
			FETCH NEXT FROM tmp_insert INTO @OLDFIELDVALUE 
			WHILE (@@FETCH_STATUS = 0)
			BEGIN
				INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
					VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, @OLDFIELDVALUE, 1)
				FETCH NEXT FROM tmp_insert INTO @OLDFIELDVALUE 
			END
			CLOSE tmp_insert
			DEALLOCATE tmp_insert
		END
	END
END

--UNDEPLOY TRIGGER INSERT
DROP TRIGGER DITS.DATA_MONITOR_ITG

--DEPLOY TRIGGER UPDATE
CREATE TRIGGER DITS.DATA_MONITOR_UTG ON DITS.DATA_MONITOR_TB FOR UPDATE AS 
BEGIN
	DECLARE @INSERT_COUNT int 
	DECLARE @DELETE_COUNT int 
	DECLARE @UPDATE_COUNT int
	DECLARE @OLDFIELDVALUE sysname
	
	IF UPDATE (DATA_ID)
	BEGIN
		SELECT @DELETE_COUNT = COUNT(*) FROM DELETED
		IF @DELETE_COUNT <> 0 
		BEGIN
			IF @DELETE_COUNT = 1 
			BEGIN
				SELECT @OLDFIELDVALUE = DATA_ID FROM DELETED
				INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
				VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, @OLDFIELDVALUE, 1)
			END
			ELSE
			BEGIN
				DECLARE tmp_delete CURSOR FOR SELECT DATA_ID FROM DELETED
				OPEN tmp_delete
				FETCH NEXT FROM tmp_delete INTO @OLDFIELDVALUE 
				WHILE (@@FETCH_STATUS = 0)
				BEGIN
					INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
						VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 3, @OLDFIELDVALUE, 1)
					FETCH NEXT FROM tmp_delete INTO @OLDFIELDVALUE 
				END
				CLOSE tmp_delete
				DEALLOCATE tmp_delete
			END
		END
	    SELECT @INSERT_COUNT = COUNT(*) FROM INSERTED
	    IF @INSERT_COUNT <> 0 
		BEGIN
			IF @INSERT_COUNT = 1 
			BEGIN
				SELECT @OLDFIELDVALUE = DATA_ID FROM INSERTED
				INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
							VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, @OLDFIELDVALUE, 1)
			END
			ELSE
			BEGIN
				DECLARE tmp_insert CURSOR FOR SELECT DATA_ID FROM INSERTED
				OPEN tmp_insert
				FETCH NEXT FROM tmp_insert INTO @OLDFIELDVALUE 
				WHILE (@@FETCH_STATUS = 0)
				BEGIN
					INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
							VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 1, @OLDFIELDVALUE, 1)
					FETCH NEXT FROM tmp_insert INTO @OLDFIELDVALUE 
				END
				CLOSE tmp_insert
				DEALLOCATE tmp_insert
			END
		END
	END
	ELSE
	BEGIN
		SELECT @UPDATE_COUNT = COUNT(*) FROM INSERTED
	    IF @UPDATE_COUNT <> 0 
		BEGIN
			IF @UPDATE_COUNT = 1 
			BEGIN
				SELECT @OLDFIELDVALUE = DATA_ID FROM INSERTED
				INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
							VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 2, @OLDFIELDVALUE, 1)
			END
			ELSE
			BEGIN
				DECLARE tmp_update CURSOR FOR SELECT DATA_ID FROM INSERTED
				OPEN tmp_update
				FETCH NEXT FROM tmp_update INTO @OLDFIELDVALUE 
				WHILE (@@FETCH_STATUS = 0)
				BEGIN
					INSERT INTO DITS.DATA_MONITOR_LOG(NSM, CLTB, OPT, CLID, INUSE) 
							VALUES('DITS.NSM', 'DITS.DATA_MONITOR_TB', 2, @OLDFIELDVALUE, 1)
					FETCH NEXT FROM tmp_update INTO @OLDFIELDVALUE 
				END
				CLOSE tmp_update
				DEALLOCATE tmp_update
			END
		END
	END 
 END

--UNDEPLOY TRIGGER UPDATE
DROP TRIGGER DITS.DATA_MONITOR_UTG

--DEPLOY TABLE
create table DITS.DATA_MONITOR_LOG(
	--自动主键
	SID          BIGINT                 not null IDENTITY,
	--名字空间
	NSM       VARCHAR(42)    	not null,
	--变更的表名
	CLTB       VARCHAR(68)    	not null,
	--变更的操作,0-删除;1-插入; 2-更新
	OPT      	INT    					not null,
	--变更数据的主键内容
	CLID        VARCHAR(256)  not null,
	--是否可用,0-不可用;1-可用
	INUSE     SMALLINT          default 1,
	--操作时间
	OPTM     DATETIME       	default getdate(),
	constraint "DITS.DATA_MONITOR_LOG.KEY" primary key (SID) 
)

--UNDEPLOY TABLE
drop table DITS.DATA_MONITOR_LOG

